package com.seraph.zys.report.model.exp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class ReportInitData extends JdbcDaoSupport{
	
	public boolean initReportData(int year,String orgCode,int orgLevel,String userName,boolean isEnforce){
		boolean bln=false;
		if(isEnforce){
			if(deleteInitDataByYear(year,orgCode))
				bln=initDataByYearAndOrgCode(year,orgCode,orgLevel);
			if(bln){
				insertReportInitDataRecord(year,orgCode,userName);
			}
		}else{
			bln=initDataByYearAndOrgCode(year,orgCode,orgLevel);
			if(bln){
				insertReportInitDataRecord(year,orgCode,userName);
			}
		}
		return bln;
	}
	
	private boolean initDataByYearAndOrgCode(int year,String orgCode,int orgLevel){
		boolean bln=false;
		List initDataList=getReportInitDataList(orgLevel);
		if(year>0 &&  initDataList!=null && initDataList.size()>0){
			List<String> sqlList=new ArrayList();
			Iterator<RptInitData> initDataIterator=initDataList.iterator();
			StringBuffer sbKey=null;
			StringBuffer sbValue=null;
			int lastSortId=0;
			String lastTableName=null;
			RptInitData rptInitData=null;
			while(initDataIterator.hasNext()){
				rptInitData=initDataIterator.next();
				if(lastTableName==null){
					lastTableName=rptInitData.getTableName();
					sbKey=new StringBuffer();
					sbValue=new StringBuffer();
					sbKey.append("fiscal_year");
					sbValue.append(String.valueOf(year));
					sbKey.append(",sort_id");
					sbValue.append(","+rptInitData.getSortId());
					sbKey.append(",from_area");
					sbValue.append(",'"+orgCode+"'");
					sbKey.append(","+rptInitData.getColName());
					sbValue.append(",'"+rptInitData.getContent()+"'");
				}else if(lastTableName.equals(rptInitData.getTableName())){
					if(rptInitData.getSortId()!=lastSortId){
						getSqlList(lastTableName,sqlList,sbKey,sbValue);
						sbKey=new StringBuffer();
						sbValue=new StringBuffer();
						sbKey.append("fiscal_year");
						sbValue.append(String.valueOf(year));
						sbKey.append(",sort_id");
						sbValue.append(","+rptInitData.getSortId());
						sbKey.append(",from_area");
						sbValue.append(",'"+orgCode+"'");
						sbKey.append(","+rptInitData.getColName());
						sbValue.append(",'"+rptInitData.getContent()+"'");
					}
					else{
						sbKey.append(","+rptInitData.getColName());
						sbValue.append(",'"+rptInitData.getContent()+"'");
					}
				}else{
					getSqlList(lastTableName,sqlList,sbKey,sbValue);
					bachSqlList(sqlList);
					sqlList.clear();
					sbKey=new StringBuffer();
					sbValue=new StringBuffer();
					sbKey.append("fiscal_year");
					sbValue.append(String.valueOf(year));
					sbKey.append(",sort_id");
					sbValue.append(","+rptInitData.getSortId());
					sbKey.append(",from_area");
					sbValue.append(",'"+orgCode+"'");
					sbKey.append(","+rptInitData.getColName());
					sbValue.append(",'"+rptInitData.getContent()+"'");
				}
				
				if(!initDataIterator.hasNext()){
					getSqlList(lastTableName,sqlList,sbKey,sbValue);
				}
				
				lastTableName=rptInitData.getTableName();
				lastSortId=rptInitData.getSortId();
				rptInitData=null;
			}
			if(sqlList!=null && sqlList.size()>0){
				bln=bachSqlList(sqlList);
			}
		}
		return bln;
	}
	
	private int insertReportInitDataRecord(int year,String orgCode,String userName){
		int num=0;
		num =this.getJdbcTemplate().update("insert into rpt_init_data_record(fiscal_year,from_area,init_status,upt_date,user_name) values("+year+",'"+orgCode+"',1,now(),'"+userName+"');");
		return num ;
	}
	
	private boolean bachSqlList(List<String> sqlList){
		boolean bln=false;
		int[] num=this.getJdbcTemplate().batchUpdate(sqlList.toArray(new String[0]));
		if(num!=null && num.length>0){
			bln=true;
		}
//		Iterator<String> iterator=sqlList.iterator();
//		while(iterator.hasNext()){
//			String sql =iterator.next();
//			System.out.println(sql+"******************************************");
//		}
		return bln;
		
	}
	private List<String> getSqlList(String tableName,List<String> sqlList,StringBuffer sbKey,StringBuffer sbValue){
		sqlList.add("insert into "+tableName+"("+sbKey.toString()+") values("+sbValue.toString()+")");
		System.out.println("insert into "+tableName+"("+sbKey.toString()+",from_area) values("+sbValue.toString()+")"+"-------------------------------------------");
		return sqlList;
	}
	private List<RptInitData> getReportInitDataList(int orgLevel){
		List<RptInitData> list=null;
		String tableNamePrefix=getRptTableNamePrefixByOrgLevel(orgLevel);
		if(tableNamePrefix!=null){
			String sql="select distinct id,table_name,sort_id,col_name,content from rpt_init_data where table_name like '"+tableNamePrefix+"%' order by table_name,sort_id";
			list=this.getJdbcTemplate().query(sql,new RowMapper(){
				public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
					RptInitData rptInitData=new RptInitData();
					rptInitData.setId(rs.getInt("id"));
					rptInitData.setTableName(rs.getString("table_name"));
					rptInitData.setSortId(rs.getInt("sort_id"));
					rptInitData.setColName(rs.getString("col_name"));
					rptInitData.setContent(rs.getString("content"));
					return rptInitData; 
				}
			});
		}
		return list;
	}
	
	private List<String> getAreaCodeList(){
		List<String> list=null;
		String sql="select distinct org_code from sc_org";
		System.out.println(sql+" ****************************************************************");
		list=this.getJdbcTemplate().query(sql,new RowMapper(){
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException { 
				   return rs.getString("org_code"); 
				}
		});
		return list;
	}
	public boolean isInitialized(int year,String orgCode){
		boolean bln=false;
		String sql ="select init_status from rpt_init_data_record where fiscal_year="+year+" and  from_area='"+orgCode+"'";
		Map map=null;
		try{
			map =this.getJdbcTemplate().queryForMap(sql);
		}catch(Exception e){
			System.out.println("this is no record.");
		}
		
		if( map!=null &&  map.size()>0){
			bln=true;
		}
		return bln;
		
	}
	public boolean deleteInitDataByYear(int year,String orgCode){
		boolean bln=false;
		String sql ="select distinct table_name from rpt_init_data";
		List list=this.getJdbcTemplate().queryForList(sql);
		List<String> sqlList=new ArrayList();
		if(list!=null && list.size()>0){
			Iterator iterator =list.iterator();
			Map map=null;
			while(iterator.hasNext()){
				map=(Map)iterator.next();
				if(map!=null){
					sqlList.add("delete from "+map.get("table_name")+" where fiscal_year="+year+" and from_area='"+orgCode+"'");
				}
				map=null;
			}
		}
		sql=null;
		sql="delete from rpt_init_data_record where fiscal_year="+year+" and from_area='"+orgCode+"'";
		sqlList.add(sql);
		sql=null;
		if(sqlList.size()>0){
			int[] num =this.getJdbcTemplate().batchUpdate(sqlList.toArray(new String[0]));
			if(num!=null && num.length>0){
				bln=true;
			}
//			Iterator<String> iterator=sqlList.iterator();
//			while(iterator.hasNext()){
//				sql=iterator.next();
//				System.out.println(sql);
//			}
		}
		return bln;
	}
	
	private String getRptTableNamePrefixByOrgLevel(int orgLevel){
		String tableNamePrefix=null;
		if(orgLevel==200){
			tableNamePrefix="pr_";
		}else if(orgLevel==300){
			tableNamePrefix="pc_";
		}else if(orgLevel==400){
			tableNamePrefix="pcc_";
		}
		
		return tableNamePrefix;
	}

}
class RptInitData{
	
	int id=0;
	
	String tableName=null;
	
	int sortId=0;
	
	public int getSortId() {
		return sortId;
	}

	public void setSortId(int sortId) {
		this.sortId = sortId;
	}

	String colName=null;
	
	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getColName() {
		return colName;
	}

	public void setColName(String colName) {
		this.colName = colName;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	String content=null;
	
}
